var express = require("express");
var uunewid = require("uuid");
var router = express.Router();
const { ohana } = require("ohana-node-orm");
const { checkProperty, checkAuth } = require("../utils/utils");
var constants = require("../utils/constants");
var moment = require("moment");
const axios = require("axios");
var COS = require("cos-nodejs-sdk-v5");
var cos = new COS({
  SecretId: "AKIDnSu88wiaSEoAKJ6f070e3E9rKZhhU1Vp",
  SecretKey: "A6nFdzCDzYtzqa8RYS8YjpmYBbnkSvBF",
});
let log = require("cf-nodejs-logging-support");
log.setLoggingLevel("info");

const tagTableName = "SAP_JIANGDU_TAG_POLICYS";
const tableName = "SAP_JIANGDU_POLICYS";

var fs = require("fs");
var Papa = require("papaparse");
let ohana_instance = new ohana(tableName);
const ohana_user = new ohana(constants.REGISTEDUSERSTABLE);

router.get("/getPolicyStatistics", function (req, res, next) {
  const user = checkAuth(req.user, constants.POLICYAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }
  getPolicyStatistics().then((result) => {
    res.send(result);
  });
});
async function getPolicyStatistics() {
  let result = await ohana_instance.raw(
    "select POLICY_TYPE, count(*) as TOTAL from " +
      tableName +
      " where POLICY_TYPE is not NULL group by POLICY_TYPE order by TOTAL desc  limit 5"
  );
  var resultData = {};
  resultData.topType = result;
  //monthly growth
  var dateQuery =
    "select count(*) as TOTAL from " +
    tableName +
    " where CREATED_AT between '" +
    moment().days(-30).format("YYYY-MM") +
    "' and '" +
    moment().format("YYYY-MM") +
    "'";
  console.log(dateQuery);
  var monthlygrowth = await ohana_user.raw(dateQuery);
  resultData.monthlygrowth = monthlygrowth[0].TOTAL;
  return resultData;
}
router.get("/getPolicyList", function (req, res, next) {
  const user = checkAuth(req.user, constants.POLICYAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }
  getPolicyShortList().then((result) => {
    res.send(result);
  });
});
async function getPolicyShortList() {
  // var conn = new ohana_instance(tableName);
  var result = await ohana_instance.find(
    [
      "POLICY_ID",
      "POLICY_TITLE",
      "CREATED_AT",
      "SUBPOLICY",
      "POLICY_TYPE",
      "POLICY_LEVEL",
    ],
    { STATUS: constants.STATUSON }
  );
  return result;
}
router.get("/from/tag", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取有某种tag的政策'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */

  // const user = checkAuth(req.user, constants.POLICYAUTH);
  // if (!user.authCheck) {
  //   res.sendStatus(403);
  // }
  let { TAG_ID } = req.query;
  getPolicyFromTag(TAG_ID)
    .then((result) => {
      res.status(200).send(result);
    })
    .catch((err) => {
      res.status(500).send(err);
    });
});

async function getPolicyFromTag(TAG_ID) {
  let result = await ohana_instance.raw(
    "select po.* from SAP_JIANGDU_TAG_POLICYS as tag left join SAP_JIANGDU_POLICYS as po on tag.POLICY_ID_POLICY_ID = po.POLICY_ID where TAG_ID_TAG_ID='" +
      TAG_ID +
      "'"
  );
  return result;
}

router.get("/top10new", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = ' 最新发布top10'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */

  // const user = checkAuth(req.user, constants.POLICYAUTH);
  // if (!user.authCheck) {
  //   res.sendStatus(403);
  // }

  top10new()
    .then((result) => {
      res.status(200).send(result);
    })
    .catch((err) => {
      res.status(500).send(err);
    });
});

async function top10new() {
  let result = await ohana_instance.raw(
    "select top 10 * from SAP_JIANGDU_POLICYS where STATUS = 'on' order by UPDATED_AT desc"
  );
  return result;
}

router.get("/top10favorite", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = '同行业收藏最多top10'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */

  const user = checkAuth(req.user, constants.POLICYAUTH);

  top10favorite(user.user_id)
    .then((result) => {
      res.status(200).send(result);
    })
    .catch((err) => {
      res.status(500).send(err);
    });
});

async function top10favorite(USER_ID) {
  // 获取该用户信息
  let userInfo = await ohana_user.find({ USER_ID: USER_ID });
  // 获取该用户所在行业
  let industry = userInfo[0].INDUSTRY;

  let top10 = await ohana_instance.raw(
    "select top 10 fa.FAVOURITE_ID,COUNT(fa.FAVOURITE_ID) AS nums from SAP_JIANGDU_FAVOURITE_LIST as fa left join SAP_JIANGDU_USERS_REGISTERED as user on fa.USER_ID_USER_ID = user.USER_ID left join SAP_JIANGDU_POLICYS as po on po.POLICY_ID = fa.FAVOURITE_ID where fa.TYPE='PO' and po.status='on' and user.INDUSTRY = '" +
      industry +
      "' group by fa.FAVOURITE_ID order by nums desc"
  );
  let result = [];
  for (let index = 0; index < top10.length; index++) {
    const element = top10[index];
    let policy = await ohana_instance.find(["POLICY_ID", "POLICY_TITLE"], {
      POLICY_ID: element.FAVOURITE_ID,
    });
    policy[0].nums = element.NUMS;
    result.push(policy[0]);
  }
  return result;
}

router.get("/pdf2CData", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = '自动根据POLICY_URL字段中存的PDF链接中的文字提取出来存入CDATA'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */

  // const user = checkAuth(req.user, constants.POLICYAUTH);
  // if (!user.authCheck) {
  //   res.sendStatus(403);
  // }

  let { POLICY_ID } = req.query;

  pdf2CData(POLICY_ID)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.status(400).send(result);
      }
    })
    .catch((err) => {
      res.status(500).send(err);
    });
});

// 有参数就改一个，没参数就把有URL但CDATA是空的全改了
async function pdf2CData(POLICY_ID) {
  let policyData = null;
  if (POLICY_ID) {
    policyData = await ohana_instance.find({ POLICY_ID: POLICY_ID });
    if (policyData[0].POLICY_URL == null || policyData[0].POLICY_URL == "") {
      return "没有POLICY_URL";
    }
  } else {
    policyData = await ohana_instance.raw(
      "select POLICY_ID, POLICY_TITLE, POLICY_URL, CDATA FROM SAP_JIANGDU_POLICYS WHERE POLICY_URL != 'null' AND POLICY_URL!='' "
    );
  }

  for (let index = 0; index < policyData.length; index++) {
    const element = policyData[index];
    // if (element.CDATA == "" || element.CDATA == null) {
    if (true) {
      try {
        let OCRRessult = await axios.post(constants.recommendURL + "pdf2text", {
          fileURL: element.POLICY_URL,
          policy_id: element.POLICY_ID,
        });
        element.CDATA = OCRRessult.data.content;
        updatePolicy(element);
      } catch (e) {
        return "OCR接口完啦：" + e;
      }
    }
  }
  return 1;
}

router.get("/insertJson", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = '录入政策'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  let { file } = req.query;
  let content = fs.readFileSync(file, "utf8");
  let parsedData = [];
  Papa.parse(content, {
    header: true,
    complete: function (results) {
      parsedData = results.data;
    },
  });

  const user = checkAuth(req.user, constants.POLICYAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
  }

  parsedData.forEach((element) => {
    if (element.POLICY_TITLE) {
      delete element.ID;
      delete element.PROCESS_DATE;
      element.USER_ID_USER_ID = user.user_id;
      element.USER_NAME = user.user_name;
      element.POLICY_ID = uunewid.v4();
      element.CREATED_AT = moment(new Date()).format("YYYY-MM-DD HH:mm:ss");
      element.UPDATED_AT = moment(new Date()).format("YYYY-MM-DD HH:mm:ss");
      insertData(element).catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
    }
  });
  res.sendStatus(200);
});

router.get("/count", async (req, res, next) => {
  // #swagger.tags = ['政策']
  // #swagger.summary = '计数'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  let { USER_ID } = req.query;
  getCount(USER_ID)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getCount(USER_ID) {
  let result = null;
  let ohana_instance = new ohana(tableName);
  if (USER_ID) {
    result = await ohana_instance.raw(
      "select count(*) as total from " +
        tableName +
        " where USER_ID_USER_ID = '" +
        USER_ID +
        "'"
    );
  } else {
    result = await ohana_instance.raw(
      "select count(*) as total from " + tableName
    );
  }
  return result[0];
}

// router.post("/upload", upload.single('file'), function (req, res, next) {
//   // #swagger.tags = ['政策']
//   // #swagger.summary = '上传文件'
//   /* #swagger.security = [{
//                "JiangduJWT": []
//   }] */
//   if (!req.file || req.file.length === 0) {
//     res.status(400).send("文件为空");
//   }
//   else {
//     let originalname = req.file.originalname;
//     let file = req.file.buffer;
//     cos.putObject({
//       Bucket: 'jiangdutest-1307977142', /* 必须 */
//       Region: 'ap-shanghai',    /* 必须 */
//       Key: "policy_files/test-" + originalname,    /* 必须 */
//       Body: file,
//     }, function (err, data) {
//       // log.error(err);
//       if (data.statusCode == 200) {
//         res.status(200).send(data.Location);
//       } else {
//         res.status(500).send("上传失败")
//       }
//     });
//   }
// })

router.get("/getLatest", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '根据日期获取最新的N条数据'
  // #swagger.description = "根据日期获得最近的N条数据参数为count.无需jwt token"
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /* #swagger.parameters['count'] = {
      description: "int",
      required: false,
} */
  const { count } = req.query;
  const policy = new ohana("SAP_JIANGDU_POLICYS");
  if (count) {
    policy
      .raw(
        "select POLICY_ID,  POLICY_URL, POLICY_TITLE, CREATED_AT, UPDATED_AT,AVATAR,QRCODE from " +
          tableName +
          " order by CREATED_AT desc limit " +
          parseInt(count)
      )
      .then((result) => {
        res.send(result);
      })
      .catch((err) => {
        log.error(err);
        res.status(500).send(err);
      });
  } else {
    res.send({ message: "错误，没有count 参数" });
  }
});

router.post("/", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取policy'
  // #swagger.description = " 获取政策列表以及搜索结果。1. 参数为空-> 所有政策, 2. 获取单个政策 参数为： POLICY_ID Sample:{POLICY_ID:'xxxxx'},3搜索功能: 参数为 searchString：''"
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy"
                    }    
                },
            }
  } */
  //get no auth needed
  const authcheck = checkAuth(req.user, constants.POLICYAUTH);
  // if (!authcheck.authCheck) {
  //   res.sendStatus(403);
  //   return;
  // }
  let { data } = req.body;
  if (data == undefined) {
    data = {};
  }
  data.user = authcheck;

  getPolicy(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/**update policy  */
router.put("/", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '更新政策 需要JWT token 没有通过则为403'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_update"
                    }  
                },
            }
    } */
  const { data } = req.body;
  // checkProperty(constants.POLICYFIELDS, data)
  const user = checkAuth(req.user, constants.POLICYAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }
  if (data.POLICY_URL) {
    var payload = { POLICY_ID: data.POLICY_ID, POLICY_URL: data.POLICY_URL };
    console.log("start OCR Process");
    startOCRProcess(payload)
      .then((result) => {
        // console.log(result);
        // res.status(200).send(payload);
      })
      .catch((err) => {
        // console.log(err);
        res.status(200).send(payload);
      });
  }
  if (data.PROCESS_DATE_TO == "-" || data.PROCESS_DATE_TO == "Invalid date") {
    delete data.PROCESS_DATE_TO;
  }
  data.UPDATED_AT = moment().format("YYYY-MM-DD");
  updatePolicy(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send(500);
      }
    })
    .catch((err) => {
      log.error(err);
      res.sendStatus(500);
    });
});

router.post("/addTags", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '给某一政策增加标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_policyTags"
                    }  
                },
            }
    } */
  const { data } = req.body;
  insertPolicyData(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else if (result == -1) {
        res.status(200).send({ code: -1, message: "请勿重复点击" });
      } else {
        res.send(400);
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/policyTags", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取标签 无需JWT token'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_policyTags"
                    }  
                },
            }
    } */
  const { data } = req.body;
  getPolicyTags(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/policyConTags", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取一个政策的条件标签 无需JWT token'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_policyTags"
                    }  
                },
            }
    } */
  const { data } = req.body;
  getPolicyConTags(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

router.post("/deleteTags", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '删除某个政策的tag'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_deleteTags"
                    }  
                },
            }
    } */
  const { data } = req.body;
  deletaPolicyTag(data)
    .then((result) => {
      res.sendStatus(200);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

/** insert policys */
router.post("/add", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '添加政策 需要JWT token 未通过为403 '
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_add"
                    }    
                },
            }
    } */
  const { data } = req.body;
  checkProperty(constants.POLICYFIELDS, data);
  const user = checkAuth(req.user, constants.POLICYAUTH);
  if (!user.authCheck) {
    res.sendStatus(403);
    return;
  }
  // check date

  if (data.PROCESS_DATE_TO == "-") {
    delete data.PROCESS_DATE_TO;
  }
  if (data.originalIndex || data.vgt_id) {
    delete data.originalIndex;
    delete data.vgt_id;
  }

  data.USER_ID_USER_ID = user.user_id;
  data.USER_NAME = user.user_name;
  data.POLICY_ID = uunewid.v4();
  data.CREATED_AT = moment().format("YYYY-MM-DD");
  data.UPDATED_AT = moment().format("YYYY-MM-DD");
  insertData(data)
    .then((result) => {
      if (result == 1) {
        var payload = {
          POLICY_ID: data.POLICY_ID,
          POLICY_URL: data.POLICY_URL,
        };
        startOCRProcess(payload)
          .then((result) => {
            res.status(200).send(payload);
          })
          .catch((err) => {
            console.log(err);
            res.status(200).send(payload);
          });
      } else {
        res.status(400).send({ message: "创建错误" });
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});
async function startOCRProcess(element) {
  try {
    console.log(constants.recommendURL + "pdf2text");
    axios
      .post(constants.recommendURL + "pdf2text", {
        policy_id: element.POLICY_ID,
        fileURL: element.POLICY_URL,
      })
      .then((result) => {
        console.log(result);
      })
      .catch((test) => {
        console.log(test);
      });
    // console.log(constants.recommendURL + "pdf2text");
  } catch (e) {
    return "OCR接口完啦：" + e;
  }
}
/** insert policys */
router.post("/addJiangbu", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '添加奖补政策 需要JWT token 未通过为403 奖补接口专用'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_add"
                    }    
                },
            }
    } */
  // basic Authentication
  if (req.headers.authorization != "Basic a2V2aW46U3VwcG9ydDEyMjA=") {
    req.sendStatus(401);
    return;
  }
  const { data } = req.body;
  var payload = { meta: {} };
  payload.data = data;
  payload.meta.BATCH_ID = uunewid.v4();
  payload.meta.CREATED_AT = moment();
  payload.meta.STATUS = constants.STATUSINITIAL;
  insertJiangbu(payload)
    .then((result) => {
      if (result == 1) {
        res.setHeader("content-type", "application/json");
        res.status(201).send({ message: "创建成功" });
      } else {
        res.status(400).send({ message: "创建时出错" });
      }
    })
    .catch((err) => {
      res.status(500).send(err);
    });
});

async function insertJiangbu(body) {
  const policy = new ohana(constants.POLICYTABLE);
  var json = JSON.stringify(body); //
  var unquoted = json.replace(/"([^"]+)":/g, "$1:");
  let resultText = unquoted.replace(new RegExp('"', "g"), "'");
  var stringText =
    "insert into " +
    constants.CAIJINGYUNINTERGRATION +
    " values(" +
    resultText +
    ")";
  const result = await policy.raw(stringText);
  return result;
}

router.get("/jiangbu", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取奖补'
  // #swagger.description = "奖补"
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  getJiangbu()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getJiangbu() {
  const policy = new ohana(constants.POLICYTABLE);
  const result = policy.raw(
    "select * from " + constants.CAIJINGYUNINTERGRATION
  );
  return result;
}

async function insertData(body) {
  const policy = new ohana("SAP_JIANGDU_POLICYS"); // new ohana('table_name');\
  try {
    // console.log(body)
    Object.keys(body).forEach((item) => {
      if (!body[item] || body[item] == "") delete body[item];
    });
    const result = await policy.insert(body);
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

router.delete("/", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '根据 POLICY_ID 删除政策。 需要参数 POLICY_ID'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_delete"
                    }  
                },
            }
    } */
  const { data } = req.body;
  deletePolicy(data)
    .then((result) => {
      if (result == 1) {
        res.sendStatus(200);
      } else {
        res.send({ message: constants.NODATAMESSAGE });
      }
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getPolicy(condition) {
  let result = [];
  const page = condition;
  const policy = new ohana("SAP_JIANGDU_POLICYS");
  let sqlString = "SELECT * FROM SAP_JIANGDU_POLICYS";
  //backend or view
  if (condition && condition.POLICY_ID) {
    result = await policy.findOne({ POLICY_ID: condition.POLICY_ID });
  } else {
    if (!condition || !condition.searchString) {
      if (!condition.user.admin) {
        sqlString += " where STATUS = '" + constants.STATUSON + "'";
        sqlString += " order BY UPDATED_AT desc ";
        if (condition.page) {
          const { page } = condition;
          const pageOffset = page.number * page.page;
          var pageText = " limit " + page.number + " offset " + pageOffset;
          sqlString += pageText;
        }
      }
      console.log(sqlString);
    } else {
      sqlString =
        sqlString +
        ' where "POLICY_TITLE" LIKE \'%' +
        condition.searchString +
        "%'";
      if (!condition.user.admin) {
        sqlString += " AND STATUS = '" + constants.STATUSON + "'";
      }
      if (condition.page) {
        const { page } = condition;
        const pageOffset = page.number * page.page;
        var pageText = " limit " + page.number + " offset " + pageOffset;
        sqlString += pageText;
      }
    }
    result = await policy.raw(sqlString);
  }
  // result.map(one=>{
  if (condition && condition.tags) {
    for (var i = 0; i < result.length; i++) {
      const oneTags = await getPolicyTags({
        POLICY_ID_POLICY_ID: result[i].POLICY_ID,
      });
      result[i].tags = oneTags;
    }
  }

  // })
  return result;
}

async function updatePolicy(body) {
  const policy = new ohana("SAP_JIANGDU_POLICYS"); // new ohana('table_name');
  const result = await policy.update(
    {
      POLICY_ID: body.POLICY_ID,
    },
    body
  );
  return result;
}
async function deletePolicy(body) {
  const policy = new ohana(tableName);
  try {
    const result = await policy.raw(
      "delete from " + tableName + " where POLICY_ID = '" + body.POLICY_ID + "'"
    );
    policy.raw(
      "delete from " +
        constants.RECOMMENDLISTTABLE +
        " where RECOMMENDED_ID ='" +
        body.POLICY_ID +
        "'"
    );
    policy.raw(
      "delete from " +
        constants.FAVORITETABLE +
        " where FAVOURITE_ID ='" +
        body.POLICY_ID +
        "'"
    );
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

async function insertPolicyData(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  try {
    const resultNum = await policyTag.find(data);
    if (resultNum.length > 0) {
      return -1;
    }
    const result = await policyTag.insert(data);
    return result;
  } catch (error) {
    log.error(error);
    return -1;
  }
}

async function deletaPolicyTag(data) {
  const policyTag = new ohana(tagTableName); // new ohana('table_name');
  const sqlText =
    "delete from " +
    tagTableName +
    " where POLICY_ID_POLICY_ID = '" +
    data.POLICY_ID_POLICY_ID +
    "' and TAG_ID_TAG_ID = '" +
    data.TAG_ID_TAG_ID +
    "'";
  const result = await policyTag.raw(sqlText);
  return result;
}

router.put("/tagsByCategories", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '批量修改一个政策某些种类的标签'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_tagsByCategories"
                    }  
                },
            }
    } */
  const { data } = req.body;
  putTagsByCategories(data)
    .then((result) => {
      res.sendStatus(200);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function putTagsByCategories(data) {
  let tags = data.tags;
  const ohana_policyTag = new ohana(tagTableName);
  const sqlText =
    "delete from " +
    tagTableName +
    " where POLICY_ID_POLICY_ID = '" +
    data.POLICY_ID +
    "' and TAG_ID_TAG_ID in (select TAG_ID from SAP_JIANGDU_TAGS where TYPE = 'PM')";
  await ohana_policyTag.raw(sqlText);
  if (tags.length != 0) {
    await ohana_policyTag.batchInsert(tags);
  }
}

async function getPolicyTags(data) {
  const policyTag = new ohana(tagTableName);
  let sqlString =
    "SELECT TAG.* FROM SAP_JIANGDU_TAGS AS TAG LEFT JOIN SAP_JIANGDU_TAG_POLICYS AS J ON TAG.TAG_ID = J.TAG_ID_TAG_ID WHERE J.POLICY_ID_POLICY_ID = '" +
    data.POLICY_ID_POLICY_ID +
    "' AND TAG.TYPE = 'PO'";
  const result = await policyTag.raw(sqlString);
  return result;
}

async function getPolicyConTags(data) {
  const policyTag = new ohana(tagTableName);
  let sqlString =
    "SELECT TAG.* FROM SAP_JIANGDU_TAGS AS TAG LEFT JOIN SAP_JIANGDU_TAG_POLICYS AS J ON TAG.TAG_ID = J.TAG_ID_TAG_ID WHERE J.POLICY_ID_POLICY_ID = '" +
    data.POLICY_ID_POLICY_ID +
    "' AND TAG.TYPE = 'PM'";
  const result = await policyTag.raw(sqlString);
  return result;
}

router.post("/policyTagsByCate", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '获取标签 无需JWT token'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*	#swagger.requestBody = {
            required: true,
            content: {
                "application/json": {
                    schema: {
                        $ref: "#/definitions/policy_policyTags"
                    }  
                },
            }
    } */
  const { data } = req.body;
  getPolicyTagsByCate(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function getPolicyTagsByCate(data) {
  const policyTag = new ohana(tagTableName);
  let sqlString =
    "SELECT TAG.* FROM SAP_JIANGDU_TAGS AS TAG LEFT JOIN SAP_JIANGDU_TAG_POLICYS AS J ON TAG.TAG_ID = J.TAG_ID_TAG_ID WHERE J.POLICY_ID_POLICY_ID = '" +
    data.POLICY_ID_POLICY_ID +
    "'";
  const result = await policyTag.raw(sqlString);
  let a = { name: "industry", data: [] };
  let b = { name: "热门产业", data: [] };
  let c = { name: "政策关键词", data: [] };
  let d = { name: "行业小类", data: [] };
  result.map((one) => {
    if (one.TAG_CATEGORY == a.name) {
      a.data.push(one);
    } else if (one.TAG_CATEGORY == b.name) {
      b.data.push(one);
    } else if (one.TAG_CATEGORY == c.name) {
      c.data.push(one);
    } else if (one.TAG_CATEGORY == d.name) {
      d.data.push(one);
    }
  });
  return { a, b, c, d };
}

router.get("/policyTagsForCascader", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '为级联选择器提供规范数据'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  policyTagsForCascader()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function policyTagsForCascader() {
  const policyTag = new ohana(tagTableName);
  const allTags = await policyTag.raw(
    "SELECT * FROM SAP_JIANGDU_TAGS WHERE TYPE = 'PO'"
  );
  let result = [
    {
      value: "industry",
      label: "行业",
      children: [],
    },
    {
      value: "热门产业",
      label: "热门产业",
      children: [],
    },
    {
      value: "政策关键词",
      label: "政策关键词",
      children: [],
    },
    {
      value: "行业小类",
      label: "行业小类",
      children: [],
    },
  ];
  allTags.map((one) => {
    one.value = one.TAG_ID;
    one.label = one.TAG_NAME;
    if (one.TAG_CATEGORY == result[0].value) {
      result[0].children.push(one);
    } else if (one.TAG_CATEGORY == result[1].value) {
      result[1].children.push(one);
    } else if (one.TAG_CATEGORY == result[2].value) {
      result[2].children.push(one);
    } else if (one.TAG_CATEGORY == result[3].value) {
      result[3].children.push(one);
    }
  });
  return result;
}

router.get("/policyConditionTagsForCascader", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '为级联选择器提供规范数据'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  policyConditionTagsForCascader()
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      log.error(err);
      res.status(500).send(err);
    });
});

async function policyConditionTagsForCascader() {
  const policyTag = new ohana(tagTableName);
  const allTags = await policyTag.raw(
    "SELECT * FROM SAP_JIANGDU_TAGS WHERE TYPE = 'PM'"
  );
  const groupingData = (data, field) => {
    let map = {};
    let dest = [];
    data.forEach((item) => {
      item["value"] = item.TAG_ID;
      item["label"] = item.TAG_VALUE;
      if (!map[item[field]]) {
        dest.push({
          value: item[field],
          label: item[field],
          children: [item],
        });
        map[item[field]] = item;
      } else {
        dest.forEach((dItem) => {
          if (dItem["value"] == item[field]) {
            dItem.children.push(item);
          }
        });
      }
    });
    return dest;
  };
  let result = groupingData(allTags, "TAG_CATEGORY");
  result.forEach((element) => {
    element.children = groupingData(element.children, "TAG_NAME");
  });
  // "TAG_NAME"
  return result;
}

router.post("/filterPolicy", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '根据一些时间的filter返回数据'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*  #swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/policy_filter"
                      }  
                  },
              }
      } */
  let { data } = req.body;
  filterPolicy(data)
    .then((result) => {
      res.send(result);
    })
    .catch((err) => {
      console.log(err);
    });
});

async function filterPolicy(condition) {
  let sqlString = "select * from SAP_JIANGDU_POLICYS";
  if (condition.length > 0) {
    sqlString += " WHERE ";
    for (let index = 0; index < condition.length; index++) {
      const element = condition[index];
      let conditionString =
        element.ATTRIBUTE + element.OPERATOR + "'" + element.VALUE + "'";
      sqlString = sqlString + conditionString;
      if (index < condition.length - 1) {
        sqlString += " AND ";
      }
    }
  }
  let result = await ohana_instance.raw(sqlString);
  return { sqlString, result };
}

router.post("/batchChangeStatus", function (req, res, next) {
  // #swagger.tags = ['政策']
  // #swagger.summary = '批量修改政策的状态或者其他字段'
  /* #swagger.security = [{
               "JiangduJWT": []
  }] */
  /*  #swagger.requestBody = {
              required: true,
              content: {
                  "application/json": {
                      schema: {
                          $ref: "#/definitions/policy_filter"
                      }  
                  },
              }
      } */
  let { data } = req.body;
  batchChangeStatus(data)
    .then(() => {
      res.sendStatus(200);
    })
    .catch((err) => {
      res.sendStatus(500);
      console.log(err);
    });
});

async function batchChangeStatus(data) {
  let field = data.field;
  let policys = data.policys;
  let allPromise = [];
  for (let index = 0; index < policys.length; index++) {
    const element = policys[index];
    allPromise.push(
      ohana_instance.update(
        {
          POLICY_ID: element.POLICY_ID,
        },
        field
      )
    );
  }
  let result = await Promise.all([...allPromise]);
  return result;
}
module.exports = router;
